
/*
'===============================================================================
'  HNG.Portal.BL.TblBannerManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HNG.Portal.BLL
{
	public class TblBannerManager : TblBannerManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblBannerManager()
		{
			// Nothing for now.
		}

        public DataTable Get_Bykeysearch(string keysearch, string columnName,int langID,string type)
        {
            string strQuery = "select * from TblBanner ";
            if (columnName != "")
                strQuery += " where " + columnName + " like N'%" + keysearch + "%'" + " and type=" + type + " and langID=" + langID + " Order by ID desc";
            else
                strQuery += " where TenBanner like N'%" + keysearch + "%'" + " and type=" + type + " and langID=" + langID + " Order by ID desc";
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, strQuery);
        }

        public DataTable SelectCommon(TblBannerEntity _TblBannerEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[5];            
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblBannerEntity.SortExpression) ? "[ID]" : _TblBannerEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblBannerEntity.SortDirection) ? " DESC" : _TblBannerEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[LangID],").Append("\n");
                sbSql.Append("  ch.[TenBanner],").Append("\n");
                sbSql.Append("  ch.[ViTri],").Append("\n");
                sbSql.Append("  ch.[Dai],").Append("\n");
                sbSql.Append("  ch.[Rong],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[NgayTao],").Append("\n");
                sbSql.Append("  ch.[NgayBatDau],").Append("\n");
                sbSql.Append("  ch.[NgayKetThuc],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[TrangThai],").Append("\n");
                sbSql.Append("  lng.[Name] LangName").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblBanner ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblBannerEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@Keyword",_TblBannerEntity.Id);
                    sbSql.Append("  AND ch.[ID] LIKE @Keyword" ).Append("\n");
                    
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenBanner] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ViTri] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Dai] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Rong] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%' + {0} + '%')", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayTao] LIKE N'%' + {0} + '%')", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayBatDau] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayKetThuc] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThuTu] LIKE N'%' + {0} + '%'", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Type] LIKE N'%' + {0} + '%')", _TblBannerEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%' + {0} + '%')", _TblBannerEntity.Condition).Append("\n");

                }


                if (!string.IsNullOrEmpty(_TblBannerEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblBannerEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.ViTri))
                {
                    _SqlParameter[2] = new SqlParameter("@ViTri", _TblBannerEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Type))
                {
                    _SqlParameter[3] = new SqlParameter("@Type", _TblBannerEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Status))
                {
                    _SqlParameter[4] = new SqlParameter("@TrangThai", _TblBannerEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblBannerEntity.StartIndex, _TblBannerEntity.EndIndex).Append("\n");
            }
            catch { return null; }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public DataTable be_selectToGridView(TblBannerEntity _TblBannerEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[6];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblBannerEntity.SortExpression) ? "[ID]" : _TblBannerEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblBannerEntity.SortDirection) ? " DESC" : _TblBannerEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[TenBanner],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[ThuTu],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[ViTri],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblBanner ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblBannerEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblBannerEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");

                }
                string strCon = _TblBannerEntity.Condition;
                if (!string.IsNullOrEmpty(_TblBannerEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[TenBanner] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThuTu] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TenBanner] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThuTu] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblBannerEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateFrom)
                {
                    _SqlParameter[2] = new SqlParameter("@CreateDateFrom", _TblBannerEntity.CreateDateFrom);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayBatDau], @CreateDateFrom) < 0").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateTo)
                {
                    _SqlParameter[3] = new SqlParameter("@CreateDateTo", _TblBannerEntity.CreateDateTo);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayKetThuc], @CreateDateTo) > 0").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.ViTri))
                {
                    _SqlParameter[4] = new SqlParameter("@ViTri", _TblBannerEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Type))
                {
                    _SqlParameter[5] = new SqlParameter("@Type", _TblBannerEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblBannerEntity.StartIndex, _TblBannerEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        public int be_UpdateOrderUp(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_BannerOrdersUp", _SqlParameter);
        }

        public int be_UpdateOrderDown(int CateID)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@ID", CateID);
            return AE.SqlHelper.ExecuteNonQuery(Conn, "sp_BannerOrdersDown", _SqlParameter);
        }

        public DataTable fe_selectBanner(TblBannerEntity _TblBannerEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[7];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblBannerEntity.SortExpression) ? "[ID]" : _TblBannerEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblBannerEntity.SortDirection) ? " DESC" : _TblBannerEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[Rong],").Append("\n");
                sbSql.Append("  ch.[Dai],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[Url]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblBanner ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblBannerEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblBannerEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");

                }
                string strCon = _TblBannerEntity.Condition;
                if (!string.IsNullOrEmpty(_TblBannerEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[TenBanner] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThuTu] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE '%{0}%'", strCon).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TenBanner] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[ThuTu] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.LangId))
                {
                    _SqlParameter[1] = new SqlParameter("@LangId", _TblBannerEntity.LangId);
                    sbSql.Append("  AND ch.[LangId] LIKE @LangId").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateFrom)
                {
                    _SqlParameter[2] = new SqlParameter("@CreateDateFrom", _TblBannerEntity.CreateDateFrom);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayBatDau], @CreateDateFrom) < 0").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateTo)
                {
                    _SqlParameter[3] = new SqlParameter("@CreateDateTo", _TblBannerEntity.CreateDateTo);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayKetThuc], @CreateDateTo) > 0").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.ViTri))
                {
                    _SqlParameter[4] = new SqlParameter("@ViTri", _TblBannerEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Type))
                {
                    _SqlParameter[5] = new SqlParameter("@Type", _TblBannerEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Status))
                {
                    _SqlParameter[6] = new SqlParameter("@TrangThai", _TblBannerEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }
                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblBannerEntity.StartIndex, _TblBannerEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }

        /*--========================HOME===============================--*/
        public DataTable fe_selectToSlideHome(TblBannerEntity _TblBannerEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[6];
            try
            {
                sbSql.Append("SELECT");
                sbSql.Append("  ch.[TenBanner],").Append("\n");
                sbSql.Append("  ch.[MoTa],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[Url]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblBanner ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblBannerEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblBannerEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateFrom)
                {
                    _SqlParameter[1] = new SqlParameter("@CreateDateFrom", _TblBannerEntity.CreateDateFrom);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayBatDau], @CreateDateFrom) < 0").Append("\n");
                }

                if (System.DateTime.MinValue != _TblBannerEntity.CreateDateTo)
                {
                    _SqlParameter[2] = new SqlParameter("@CreateDateTo", _TblBannerEntity.CreateDateTo);
                    sbSql.Append("  AND DATEDIFF(Minute, ch.[NgayKetThuc], @CreateDateTo) > 0").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.ViTri))
                {
                    _SqlParameter[3] = new SqlParameter("@ViTri", _TblBannerEntity.ViTri);
                    sbSql.Append("  AND ch.[ViTri] LIKE @ViTri").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblBannerEntity.Type))
                {
                    _SqlParameter[4] = new SqlParameter("@Type", _TblBannerEntity.Type);
                    sbSql.Append("  AND ch.[Type] LIKE @Type").Append("\n");
                }
                sbSql.Append("ORDER BY ch.[ThuTu] ASC").Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }
	}
}
